﻿Imports MySql.Data.MySqlClient
Imports MySql.Data.Types

Public Class frmSettings
    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        Dim bOK As Boolean = True
        If txtServer.Text = "" Then bOK = False
        If txtPort.Text = "" Then bOK = False
        If txtDB.Text = "" Then bOK = False
        If txtUser.Text = "" Then bOK = False
        If txtPass.Text = "" Then bOK = False

        If Not bOK Then
            MsgBox("One or more fields are empty!", "Warning")
        
        End If

        My.Settings.db_server = txtServer.Text
        My.Settings.db_port = Val(txtPort.Text)
        My.Settings.db_db = txtDB.Text
        My.Settings.db_user = txtUser.Text
        My.Settings.db_pass = txtPass.Text
        My.Settings.db_DownloadRecords = Val(txtDLRecords.Text)

        Me.Close()
    End Sub

    Private Sub cmdVerify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdVerify.Click
        Dim strReturn As String = "Unknown"
        Me.Enabled = False
        Dim c As Cursor = Me.Cursor
        Me.Cursor = Cursors.WaitCursor
        Dim conn As New MySqlConnection()
        Dim cmd As MySqlCommand = Nothing
        Dim reader As MySqlDataReader = Nothing
        conn.ConnectionString = "Data Source=" & txtServer.Text & ";" & _
                "User Id='" & txtUser.Text & "';" & _
                "Password='" & txtPass.Text & "';" &
                "Port=" & Val(txtPort.Text) & ";" & _
                "Database='" & txtDB.Text & "'"

        Try
            conn.Open()

            'Check tables
            cmd = conn.CreateCommand
            cmd.CommandText = "SHOW TABLES"

            reader = cmd.ExecuteReader
            Dim bOK As Boolean = True
            If reader.HasRows Then
                Dim strField As String
                Dim bTables As New Collection
                bTables.Add(New classProperty("Trades", False), "Trades")

                Do While reader.Read
                    strField = reader.GetString(0)
                    If bTables.Contains(strField) Then
                        CType(bTables(strField), classProperty).Value = True
                    End If
                Loop
                'check if everything is in place..
                Dim bOK_2 As Boolean = True
                For Each x As classProperty In bTables
                    If x.Value = False Then
                        bOK_2 = False
                        Exit For
                    End If
                Next
                If Not bOK_2 Then
                    Dim ex As New Exception("Database is missing fields!")
                End If
            Else
                Dim ex As New Exception("Database is not initialized!")
                Throw ex
            End If

            '        cmd.CommandText = "DELETE FROM Timer2 WHERE id = @id"
            '        cmd.Parameters.AddWithValue("@id", c.ID)

            'check content of tables

            strReturn = "OK"
        Catch ex As MySqlException
            Select Case ex.Number
                Case 1042 : strReturn = "Server is unreachable."
                Case 1045 : strReturn = "Wrong username or password!"
                Case 1046 : strReturn = "No Database selected!"
                Case Else
                    MsgBox(ex.Number & ": " & ex.Message)
                    Debug.Print(ex.Number, ex.Message, ex.InnerException)
            End Select
        Catch ex As Exception
            strReturn = ex.Message
            Debug.Print(ex.Message, ex.InnerException)
        Finally
            If Not conn.State = ConnectionState.Closed Then conn.Close()
            If Not reader Is Nothing Then reader = Nothing
            If Not cmd Is Nothing Then cmd = Nothing
        End Try

        txtStatus.Text = strReturn
        If strReturn = "OK" Then
            My.Settings.db_init = True
        End If

        Me.Enabled = True
        Me.Cursor = c
    End Sub

    Private Sub frmSettings_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        txtServer.Text = My.Settings.db_server
        txtPort.Text = My.Settings.db_port
        txtDB.Text = My.Settings.db_db
        txtUser.Text = My.Settings.db_user
        txtPass.Text = My.Settings.db_pass
        txtDLRecords.Text = My.Settings.db_DownloadRecords
        If My.Settings.db_init Then
            txtStatus.Text = "OK"
        End If
    End Sub

    Private Sub lblStatus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lblStatus.Click

    End Sub

    Private Sub cmdInit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdInit.Click
        Me.Enabled = False
        Dim c As Cursor = Me.Cursor
        Me.Cursor = Cursors.WaitCursor
        Dim strReturn As String = "Unknown"
        Dim ret = MsgBox("All old data may be deleted!" & vbNewLine & "Be sure to take a backup before you proceed!" & vbNewLine & vbNewLine & "Proceed?", vbYesNo)
        If ret = MsgBoxResult.Yes Then

            Dim conn As New MySqlConnection()
            Dim cmd As MySqlCommand = Nothing
            Dim reader As MySqlDataReader = Nothing
            conn.ConnectionString = "Data Source=" & txtServer.Text & ";" & _
                    "User Id='" & txtUser.Text & "';" & _
                    "Password='" & txtPass.Text & "';" &
                    "Port=" & Val(txtPort.Text) & ";" & _
                    "Database='" & txtDB.Text & "'"

            Try
                conn.Open()

                Dim cMysql As New classMySQLSettings
                'sjekk om tabbellen ligger her:
                cmd = conn.CreateCommand
                cmd.CommandText = "SHOW TABLES"
                reader = cmd.ExecuteReader
                Dim bFoundTrades As Boolean = False
                Do While reader.Read
                    If cMysql.Tables.Contains(reader.GetString(0)) Then
                        CType(cMysql.Tables(reader.GetString(0)), classProperty).Value = True
                    End If
                Loop
                reader.Close()
                'drop tables
                'cmd = conn.CreateCommand
                'cmd.CommandText = "DROP TABLE IF EXISTS Trades"
                'cmd.ExecuteNonQuery()
                'cmd.Dispose()
                For Each Table As classProperty In cMysql.Tables
                    If Table.Value Then
                        'check if all columns is present

                        cmd = conn.CreateCommand
                        cmd.CommandText = "DESCRIBE Trades"
                        reader = cmd.ExecuteReader
                        Do While reader.Read
                            If cMysql.TradesCollumns.Contains(reader.GetString(0)) Then
                                CType(cMysql.TradesCollumns(reader.GetString(0)), classProperty).Value = True
                            End If
                        Loop
                        reader.Close()

                        'if some tables does not excist, add them:
                        For Each Column As classProperty In cMysql.TradesCollumns
                            If Column.Value = False Then
                                cmd = conn.CreateCommand
                                cmd.CommandText = "ALTER TABLE `Trades` ADD COLUMN `" & Column.Name & "` " & Column.value2 & " AFTER `updated`"
                                cmd.ExecuteNonQuery()
                            End If
                        Next


                    Else
                        'create new tables
                        cmd = conn.CreateCommand
                        cmd.CommandText = Table.value2
                        cmd.ExecuteNonQuery()
                    End If
                Next
                strReturn = "OK"
            Catch ex As MySqlException
                Select Case ex.Number
                    Case 1042 : strReturn = "Server is unreachable."
                    Case 1045 : strReturn = "Wrong username or password!"
                    Case 1046 : strReturn = "No Database selected!"
                    Case 1142 : strReturn = "Not authorized!"
                    Case Else
                        MsgBox(ex.Number & ": " & ex.Message)
                        Debug.Print(ex.Number, ex.Message, ex.InnerException)
                End Select
            Catch ex As Exception
                strReturn = ex.Message
                Debug.Print(ex.Message, ex.InnerException)
            Finally
                If Not conn.State = ConnectionState.Closed Then conn.Close()
                If Not reader Is Nothing Then reader = Nothing
                If Not cmd Is Nothing Then cmd = Nothing
            End Try
        End If
        Me.Enabled = True
        Me.Cursor = c
        txtStatus.Text = strReturn
        If strReturn = "OK" Then
            My.Settings.db_init = True
        End If

    End Sub

    Private Sub txtStatus_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtStatus.TextChanged

    End Sub
End Class
